package test.excel;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintStream;
import java.io.PrintWriter;

import test.BeanxmlFactory;
import cn.com.pxto.model.Guest;
import cn.com.pxto.model.Lesson;
import cn.com.pxto.model.LessonType;
import cn.com.pxto.model.School;
import cn.com.pxto.service.GuestManager;
import cn.com.pxto.service.SchoolManager;

import jxl.Cell;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class LoadExcel {
	private static String filePath = "F:\\JavaSoft\\eclipse-jee-europa-win32\\work\\pxto\\build\\classes\\test\\excel\\default.xls";
	private static Sheet sheet;
	private static GuestManager guestManager;
	private static SchoolManager schoolManager;

	private LoadExcel() throws BiffException, IOException {
		// 通过Workbook的静态方法getWorkbook选取Excel文件
		Workbook workbook = Workbook.getWorkbook(new File(filePath));

		// 通过Workbook的getSheet方法选择第一个工作簿（从0开始）
		sheet = workbook.getSheet(0);

		BeanxmlFactory beans = BeanxmlFactory.getInstance();

		guestManager = (GuestManager) beans.getBean("guestManager");
		schoolManager = (SchoolManager) beans.getBean("schoolManager");
	}

	public void getRowCell(int y) {
		Guest guest = new Guest();
		Integer ltid = null;
		for (int x = 0; x < 7; x++) {
			Cell cell = sheet.getCell(x, y);
			switch (x) {
			case 0:
				// logonDate
				DateCell dateCell = (DateCell) cell;
				guest.setLogonDate(dateCell.getDate().getTime());
				break;
			case 1:
				// lessonTypeId
				ltid = setGuestLessonTypeId(guest, cell);
				break;
			case 2:
				// name
				guest.setName(cell.getContents());
				break;
			case 3:
				// lessonId
			    setGuestLessonId(guest, cell, ltid);
				guest.setLesson(cell.getContents());
				break;
			case 4:
				// schoolId
				setGuestSchoolId(guest, ltid, cell);
				break;
			case 5:
				// remark;
				guest.setRemark(cell.getContents());
				break;
			case 6:
				// tel
				guest.setTel(cell.getContents());
				break;
			}
		}
		guest.setUserId(2);
		guestManager.createGuest(guest);
		System.out.println("sucess:" + guest.getName());
	}

	private static void setGuestSchoolId(Guest guest, Integer ltid, Cell cell) {
		String schoolName = cell.getContents();
		School school = schoolManager.getSchoolByName(schoolName);
		if (school == null) {
			school = new School();
			school.setName(schoolName);
			// TODO school lessonTypeStr
			school.setLessonTypeStr(ltid + ",");
			schoolManager.createSchool(school);
		} else {
			// TODO school lessonTypeStr
			String str = school.getLessonTypeStr();
			if (str.indexOf(ltid + ",") == -1) {
				str += ltid + ",";
				school.setLessonTypeStr(str);
				schoolManager.updateSchool(school);
			}
		}
		guest.setSchoolId(school.getSid());
	}

	private static void setGuestLessonId(Guest guest, Cell cell, Integer ltid) {
		String lessonName = cell.getContents();
		Lesson lesson = schoolManager.getLessonByName(lessonName);
		if (lesson == null) {
			lesson = new Lesson();
			lesson.setName(lessonName);
			lesson.setType(ltid);
			schoolManager.createLesson(lesson);
		}
	}

	private static Integer setGuestLessonTypeId(Guest guest, Cell cell) {
		String lessonTypeName = cell.getContents();
		LessonType lessonType = schoolManager
				.getLessonTypeByName(lessonTypeName);
		if (lessonType == null) {
			lessonType = new LessonType();
			lessonType.setName(lessonTypeName);
			schoolManager.createLessonType(lessonType);
		}
		guest.setLessonTypeId(lessonType.getLtid());
		return lessonType.getLtid();
	}

	/**
	 * @param args
	 * @throws IOException
	 * @throws BiffException
	 */
	public static void main(String[] args) throws BiffException, IOException {
		LoadExcel excel = new LoadExcel();
		// total 98
		for (int k = 2; k < 98; k++) {
			try {
				excel.getRowCell(k);
			} catch (Exception e) {
				System.out.println(k);
				e.printStackTrace();
			}
		}
	/*	File log = new File("F:\\log.txt");
		PrintStream oldPrintStream = System.out;
		ByteArrayOutputStream bos = new ByteArrayOutputStream();
		System.setOut(new PrintStream(bos));
		System.setOut(oldPrintStream);
		FileOutputStream fs = new FileOutputStream(log);
		byte bytes[] = bos.toString().getBytes();
		fs.write(bytes);
		fs.close();*/
	}

}
